package com.ar4j.bench.jdbc.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.IItem;
import com.ar4j.bench.common.domain.IProduct;
import com.ar4j.bench.common.domain.ProductWithItems;
import com.ar4j.bench.common.service.IProductService;
import com.ar4j.bench.jdbc.domain.Product;

/**
 * A product service implemented using spring's JdbcTemplate
 */
@Service
public class ProductJdbcService implements InitializingBean, IProductService {
  private static final String PRODUCT_BY_ID = "SELECT ID, CATEGORY_ID, NAME, IMAGE, DESCRIPTION FROM PRODUCT WHERE ID = ?";
  private static final String PRODUCTS_BY_CATEGORY_ID = "SELECT ID, CATEGORY_ID, NAME, IMAGE, DESCRIPTION FROM PRODUCT WHERE CATEGORY_ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM PRODUCT WHERE ID = ?";
  
  private static final ProductMapper MAPPER = new ProductMapper();
  
  @Autowired
  private DataSource dataSource;
  
  @Autowired
  private ItemJdbcService itemService;
  
  private JdbcTemplate template;

  public Long addProduct(IProduct product) {
    KeyHolder holder = new GeneratedKeyHolder();
    template.update(new ProductInsert(product), holder);
    return holder.getKey().longValue();
  }

  public IProduct findById(Long id) {
    List<IProduct> results = template.query(PRODUCT_BY_ID, new Object[] { id }, MAPPER);
    if(results.size() != 1) {
      throw new IncorrectResultSizeDataAccessException(1, results.size());
    }
    
    return results.get(0);
  }

  public List<IProduct> findByCategoryId(Long categoryId) {
    return template.query(PRODUCTS_BY_CATEGORY_ID, new Object[] { categoryId }, MAPPER);
  }

  public void updateProduct(IProduct product) {
    template.update(new ProductUpdate(product));
  }

  public void removeById(Long id) {
    template.update(DELETE_BY_ID, id);
  }

  public Long addProductWithItems(ProductWithItems product) {
    Long productId = addProduct(product.getProduct());
    for(IItem item : product.getItems()) {
      item.setProductId(productId);
      itemService.addItem(item);
    }
    return productId;
  }

  public ProductWithItems findProductWithItemsById(Long id) {
    ProductWithItems out = new ProductWithItems();
    out.setProduct(findById(id));
    out.setItems(itemService.findByProductId(id));
    return out;
  }

  public List<ProductWithItems> findProductWithItemsByCategoryId(Long categoryId) {
    List<IProduct> products = findByCategoryId(categoryId);
    List<ProductWithItems> out = new ArrayList<ProductWithItems>();
    for(IProduct product : products) {
      ProductWithItems element = new ProductWithItems();
      element.setProduct(product);
      element.setItems(itemService.findByProductId(product.getId()));
      out.add(element);
    }
    
    return out;
  }

  public void updateProductWithItems(ProductWithItems product) {
    updateProduct(product.getProduct());
    
    Map<Long, IItem> fromDb = new HashMap<Long, IItem>();
    for(IItem item : itemService.findByProductId(product.getProduct().getId())) {
      fromDb.put(item.getId(), item);
    }
    
    for(IItem item : product.getItems()) {
      if(item.getId() == null || !fromDb.containsKey(item.getId())) {
        itemService.addItem(item);
      } else {
        itemService.updateItem(item);
        fromDb.remove(item.getId());
      }
    }
    
    for(IItem item : fromDb.values()) {
      itemService.removeById(item.getId());
    }
  }
  
  public void removeProductWithItemsById(Long id) {
    for(IItem item : itemService.findByProductId(id)) {
      itemService.removeById(item.getId());
    }
    
    removeById(id);
  }

  @Override
  public void afterPropertiesSet() throws Exception {
    template = new JdbcTemplate(dataSource);
  }

  private static class ProductInsert implements PreparedStatementCreator {
    private static final String SQL = "INSERT INTO PRODUCT(CATEGORY_ID, NAME, IMAGE, DESCRIPTION) VALUE (?, ?, ?, ?)";
    
    private IProduct product;
    
    public ProductInsert(IProduct product) {
      this.product = product;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
      
      int nextParameter = 1;
      ps.setLong(nextParameter++, product.getCategoryId());
      ps.setString(nextParameter++, product.getName());
      ps.setString(nextParameter++, product.getImage());
      ps.setString(nextParameter++, product.getDescription());
      
      return ps;
    }
  }
  
  public static class ProductMapper implements RowMapper<IProduct> {

    @Override
    public IProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
      IProduct out = new Product();
      
      int nextParameter = 1;
      out.setId(rs.getLong(nextParameter++));
      out.setCategoryId(rs.getLong(nextParameter++));
      out.setName(rs.getString(nextParameter++));
      out.setImage(rs.getString(nextParameter++));
      out.setDescription(rs.getString(nextParameter++));
      
      return out;
    }
  }

  private static class ProductUpdate implements PreparedStatementCreator {
    private static final String SQL = "UPDATE PRODUCT SET CATEGORY_ID = ?, NAME = ?, IMAGE = ?, DESCRIPTION = ? WHERE ID = ?";
    
    private IProduct product;
    
    public ProductUpdate(IProduct product) {
      this.product = product;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL);
      
      int nextParameter = 1;
      ps.setLong(nextParameter++, product.getCategoryId());
      ps.setString(nextParameter++, product.getName());
      ps.setString(nextParameter++, product.getImage());
      ps.setString(nextParameter++, product.getDescription());
      ps.setLong(nextParameter++, product.getId());
      
      return ps;
    }
  }
}
